#!/usr/bin/env python3
# -*- coding: utf8 -*-


import os
import sys
import tarfile
import sqlite3
import requests
from lxml import etree # What the fuck, Python: https://docs.python.org/3.9/library/xml.html#xml-vulnerabilities
from http import HTTPStatus
from datetime import datetime


if __name__ == '__main__':

    versions = [
        '10.14.5',
        '10.14.4',
        '10.14.3',
        '10.14.2',
        '10.14.1',
        '10.14',
        '10.13.6',
        '10.13.5',
        '10.13.4',
        '10.13.3',
        '10.13.2',
        '10.13.1',
        '10.13',
        '10.12.6',
        '10.12.5',
        '10.12.4',
        '10.12.3',
        '10.12.2',
        '10.12.1',
        '10.12',
        '10.11.6',
        '10.11.5',
        '10.11.4',
        '10.11.3',
        '10.11.2',
        '10.11.1',
        '10.11',
        '10.10.5',
        '10.10.4',
        '10.10.3',
        '10.10.2',
        '10.10.1',
        '10.10',
        '10.9.5',
        '10.9.4',
        '10.9.3',
        '10.9.2',
        '10.9.1',
        '10.9',
        '10.8.5',
        '10.8.4',
        '10.8.3',
        '10.8.2',
        '10.8.1',
        '10.8',
        '10.7.5',
        '10.7.4',
        '10.7.3',
        '10.7.2',
        '10.7.1',
        '10.7',
        '10.6.8',
        '10.6.7',
        '10.6.6',
        '10.6.5',
        '10.6.4',
        '10.6.3',
        '10.6.2',
        '10.6.1',
        '10.6',
        '10.5.8',
        '10.5.7',
        '10.5.6',
        '10.5.5',
        '10.5.4',
        '10.5.3',
        '10.5.2',
        '10.5.1',
        '10.5',
        '10.4.11.x86',
        '10.4.11.ppc',
        '10.4.10.x86',
        '10.4.10.ppc',
        '10.4.9.x86',
        '10.4.9.ppc',
        '10.4.8.ppc',
        '10.4.8.x86',
        '10.4.7.ppc',
        '10.4.7.x86',
        '10.4.6.ppc',
        '10.4.6.x86',
        '10.4.5.x86',
        '10.4.5.ppc',
        '10.4.4.x86',
        '10.4.4.ppc',
        '10.4.3',
        '10.4.2',
        '10.4.1',
        '10.4',
        '10.3.9',
        '10.3.8',
        '10.3.7',
        '10.3.6',
        '10.3.5',
        '10.3.4',
        '10.3.3',
        '10.3.2',
        '10.3.1',
        '10.3',
        '10.2.8',
        '10.2.8.G5',
        '10.2.7',
        '10.2.6',
        '10.2.5',
        '10.2.4',
        '10.2.3',
        '10.2.2',
        '10.2.1',
        '10.2',
        '10.1.5',
        '10.1.4',
        '10.1.3',
        '10.1.2',
        '10.1.1',
        '10.1',
        '10.0.4',
        '10.0.3',
        '10.0.2',
        '10.0.1',
        '10.0',
    ]

    attributes = {
        'OpenSourceImportDate': 'imported',
        'OpenSourceLicense': 'license',
        'OpenSourceLicenseFile': 'license_file',
        'OpenSourceModifications': 'modifications',
        'OpenSourceProject': 'name',
        'OpenSourceSHA1': 'sha1',
        'OpenSourceURL': 'url',
        'OpenSourceVersion': 'version',
        'OpenSourceWebsite': 'website',
    }

    db = sqlite3.connect('openapple.db')
    c = db.cursor()

    c.execute("""
        CREATE TABLE IF NOT EXISTS `projects` (
            `id`       INTEGER PRIMARY KEY AUTOINCREMENT,
            `macos`    TEXT NOT NULL,
            `name`     TEXT NOT NULL,
            `build`    TEXT NOT NULL,
            `modified` TEXT NOT NULL,
            `done`     INT  NOT NULL DEFAULT 0,
            UNIQUE(`macos`, `name`)
        )
    """)
    c.execute("""
        CREATE TABLE IF NOT EXISTS `opensource` (
            `id`            INTEGER PRIMARY KEY AUTOINCREMENT,
            `project_id`    INTEGER NOT NULL,
            `macos`         TEXT NOT NULL,
            `name`          TEXT,
            `imported`      TEXT,
            `license`       TEXT,
            `license_file`  TEXT,
            `modifications` TEXT,
            `sha1`          TEXT,
            `url`           TEXT,
            `version`       TEXT,
            `website`       TEXT,
            UNIQUE(`macos`, `name`)
        )
    """)
    db.commit()

    for version in versions:

        main = requests.get('https://opensource.apple.com/plist/macos-%s.plist' % version.replace('.', ''))

        if main.status_code == HTTPStatus.OK:
            projectList = etree.fromstring(main.content)[0].find('dict')

            for i, child in enumerate(projectList):

                if child.tag != 'key':
                    continue

                name = child.text

                # Skip project if done in db

                c.execute('SELECT COUNT(*) FROM `projects` WHERE `name`=(?) AND `done`=TRUE', (name, ))

                if c.fetchone()[0]:
                    continue

                buildVersion = projectList[i+1].find('string').text

                tar = requests.get('https://opensource.apple.com/tarballs/%s/%s-%s.tar.gz' % (name, name, buildVersion))

                if tar.status_code == HTTPStatus.OK:
                    with open('temp.tar.gz', 'wb') as f:
                        f.write(tar.content)
                else:
                    print('Error! %s' % name)
                    continue
                
                tar = tarfile.open('temp.tar.gz')

                members = tar.getmembers()
                lastModified =  datetime.utcfromtimestamp(int(members[0].get_info()['mtime'])).strftime('%Y-%m-%d %H:%M:%S')

                try:
                    c.execute('INSERT INTO `projects` (`macos`, `name`, `build`, `modified`) VALUES (?, ?, ?, ?) ', (version, name, buildVersion, lastModified))
                    db.commit()
                except sqlite3.IntegrityError:
                    pass
                finally:
                    c.execute('SELECT `id` FROM `projects` WHERE `name`=(?)', (name, ))
                    projectId = c.fetchone()[0]
                
                plists = [member for member in members if '.plist' in member.name or '.partial' in member.name]

                for member in plists:
                    try:
                        plist = etree.fromstring(tar.extractfile(member).read())

                        if plist.tag != 'dict':

                            opensource = plist.find('dict')

                            if opensource == None:
                                opensource = plist.find('array')

                                if opensource == None:
                                    continue

                                opensource = opensource.find('dict')
                            
                            if opensource == None:
                                continue
                        else:
                            opensource = plist

                    except etree.XMLSyntaxError:
                        # Skip invalid xml files
                        continue

                    relevant = {}
                    
                    for index, attribute in enumerate(opensource):

                        if attribute.tag != 'key':
                            continue

                        if attribute.text in attributes.keys():
                            relevant[attributes[attribute.text]] = opensource[index+1].text

                    if not relevant:
                        continue

                    relevant['macos'] = version
                    relevant['project_id'] = projectId
                    for attribute in attributes.values():
                        if not attribute in relevant:
                            relevant[attribute] = ''

                    try:
                        c.execute('INSERT INTO opensource VALUES (NULL, :macos, :project_id, :name, :imported, :license, :license_file, :modifications, :sha1, :url, :version, :website)', relevant)
                        db.commit()
                    except sqlite3.IntegrityError:
                        pass
                    
                
                c.execute('UPDATE `projects` SET `done`=TRUE WHERE `id`=(?)', (projectId, ))
                db.commit()

    os.remove('temp.tar.gz')

